package cn.singno.bob.jpa.dao;

import java.util.List;
import java.util.Map;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import cn.singno.bob.jpa.base.BaseRepository;
import cn.singno.bob.jpa.entity.User;




public interface UserDao  extends BaseRepository<User, String> {

	public List<User> findByName(String name);
	
	@Query("SELECT o From User o WHERE id = :id and o.name = :name")
	public User findBy(@Param("id") String id, @Param("name")String name);
	
	@Query("SELECT o From User o WHERE o.name = :name")
	public Page<User> findByName(@Param("name") String name, Pageable pageable);
	
	@Query("SELECT o From User o WHERE o.name = ?1")
	public List<User> find2ByName(String name);
	
	@Query(value = "SELECT o.* From User o left join Student s on o.id = s.userId where s.userId = ?1 " , nativeQuery = true)
	public User leftJoin(String userId);
	
	
	@Query(value = "SELECT o.* From User o left join Student s on o.id = s.userId where s.userId = ?1 " , nativeQuery = true)
	public List<Map<String, Object>> leftJoin2(String userId);
	
	/*@Query(value = "SELECT o.* From User o left join Student2 s on o.id = s.userId where s.userId = ?1 " , nativeQuery = true,
			countQuery = "SELECT count(o.id) From User o left join Student s on o.id = s.userId where s.userId = ?1 ")
	public Page<User> leftJoin(String userId, Pageable pageable);\
	在启动的时候就加入了进去
	*/
	
	@Query("SELECT new Map(o.name,o.age) From User o WHERE o.name = ?1")
	public List<Map<String, Object>> findMap3(String userName);
	
	
	@Modifying
	@Query("update User set name = ?1 where id = ?2")
	public int update(String name, String id) ;
	
	@Query("SELECT new Map(o.name,o.age) From User o WHERE o.id = ?1")			//如果字段不取别名，将默认0=12,1=haha  
	//@QueryHints缓存																//如果不自定义countQuery默认select count(*) from o  分页数据将不正确
	public Map<String, Object> findMap(String userId);
	
	@Query("SELECT new Map(o.name as name,o.age as age) From User o WHERE o.name = ?1")
	public Page<Map<String, Object>> findMap4(String userName,  Pageable pageable);
	
	
}
